package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.web.dao.AdSeller;
import cn.com.dashihui.web.dao.Seller;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.User;

public class ChangeMoneyService {

	/**
	 * 查询所有营业
	 * @return
	 */
	public List<Store> findStoreList() {
		return Store.me().find("select id,storeid,title,city from t_dict_store");
	}
	/**
	 * 查询所有公司登录
	 * @return
	 */
	public List<AdSeller> findAdSeller() {
		return AdSeller.me().find("select * from t_auth_admin_seller");
	}
	/**
	 * 查询所有公司
	 * @return
	 */
	public List<Seller> findAllSeller() {
		return Seller.me().find("select * from t_bus_seller");
	}
	/**
	 * 根据sellerid查询serller
	 */
	public Seller findSellerById(String sellerid) {
		return Seller.me().findFirst("select id from t_bus_seller where sellerId=?",sellerid);
	}
	/**
	 * 根据storeid查询store
	 * @param store
	 * @return
	 */
	public Store findStoreById(String store) {
		return Store.me().findFirst("select * from t_dict_store where storeid=?",store);
	}
	/**
	 * 查询所有营业部
	 * @return
	 */
	public AdSeller findAdSellerByName(String trueName) {
		return AdSeller.me().findFirst("select id from t_auth_admin_seller where trueName = ?",trueName);
	}
	/**
	 * 会员信息
	 * @param pageNum
	 * @param pageSize
	 * @param phone
	 * @param card
	 * @param sBeginDate
	 * @param sEndDate
	 * @param beginDate
	 * @param endDate
	 * @param title
	 * @param dao
	 * @param taici
	 * @param state
	 * @return
	 */
	public Page<Record> findByPage(int pageNum, int pageSize, String phone, String card, String sBeginDate, String sEndDate,
			String address, int toBossThreeId, int toBossTwoId, int toBossOneId, String taici, String state){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user_changeToMoney TM "
				+ "inner join t_bus_user A on TM.userId=A.id "
				+ "left join t_bus_user G on G.id=A.inviteid "
				+ "left join t_bus_user_card H on A.id=H.userid "
				+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
				+ "left join t_bus_seller SE on SE.id=A.toBossThreeId "
				+ "left join t_dict_store ST on ST.id=A.toBossTwoId "
				+ "left join (select userid,count(id) as countcum,sum(amount) as allCount from  t_bus_order where orderState NOT in(1,5,7,9)  group by userid) C on C.userid= A.id "
				+ " left join (select id,inviteid,COUNT(inviteid) AS userNum from t_bus_user where 1=1 group by inviteid)CNum on  CNum.inviteid= A.id  "
				+ " LEFT JOIN (select phone,COUNT(id) as coun from t_vip_user_orderdail GROUP BY phone) E on E.phone=A.phone where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND A.birthday BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(toBossThreeId!=0){
			sBuffer.append(" and A.toBossThreeId in ('"+toBossThreeId+"')");
		}
		if(toBossTwoId!=0){
			sBuffer.append(" and A.toBossTwoId in ('"+toBossTwoId+"')");
		}
		if(toBossOneId!=0){
			sBuffer.append(" and A.toBossOneId in ('"+toBossOneId+"')");
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		if(StrKit.notBlank(state)){
			sBuffer.append(" and TM.state in ('"+state+"')");
		}
		sBuffer.append(" ORDER BY TM.state asc,A.createDate DESC,A.id desc ");
		return Db.paginate(pageNum, pageSize, "SELECT A.*, ifnull(C.countcum, 0) AS countnum,CNum.userNum ,G.id AS pareId, G.name AS pareName, S.trueName"
				+ ", SE.sellerName AS sellerName, ST.title AS storeName,"
				+ "TM.state AS tmState,TM.money AS tmMoney,TM.jypoint AS tmJypoint,TM.createTime AS tmCreateTime,TM.id AS tmId,TM.orderNo AS tmOrderNo,TM.changeName,H.custName as cardUserName", sBuffer.toString(),params.toArray());
	}
	/**
	 * 查询所需积分”总和，“现金价格”总和,退款价格总和，实际收入
	 */

	public Record findTotal(int pageNum, int pageSize, String phone, String card, String sBeginDate, String sEndDate,
			String address, int toBossThreeId, int toBossTwoId, int toBossOneId, String taici, String state,String name){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT "
				+ "IFNULL(ROUND(SUM(money), 2),0.00) total, "
				+ "IFNULL(ROUND(SUM(jypoint), 2),0.00) jypoint, "
				+ "IFNULL(ROUND(SUM(amount1), 2),0.00) accept, "
				+ "IFNULL(ROUND(SUM(amount2), 2),0.00) cancel "
				+ "from ( select "
				+ "case when TM.state=3 then TM.money else 0.00 end as amount1, "
				+ "case when TM.state=2 then TM.money else 0.00 end as amount2, "
				+ "A.money,A.jypoint "
				+ "FROM t_bus_user_changeToMoney TM "
				+ "inner join t_bus_user A on TM.userId=A.id "
				+ "left join t_bus_user G on G.id=A.inviteid "
				+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
				+ "left join t_bus_seller SE on SE.id=A.toBossThreeId "
				+ "left join t_dict_store ST on ST.id=A.toBossTwoId "
				+ "left join (select userid,count(id) as countcum,sum(amount) as allCount from  t_bus_order where orderState NOT in(1,5,7,9)  group by userid) C on C.userid= A.id "
				+ " left join (select id,inviteid,COUNT(inviteid) AS userNum from t_bus_user where 1=1 group by inviteid)CNum on  CNum.inviteid= A.id  "
				+ " LEFT JOIN (select phone,COUNT(id) as coun from t_vip_user_orderdail GROUP BY phone) E on E.phone=A.phone where 1=1 ");
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(name)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+name+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND A.birthday BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(toBossThreeId!=0){
			sBuffer.append(" and A.toBossThreeId in ('"+toBossThreeId+"')");
		}
		if(toBossTwoId!=0){
			sBuffer.append(" and A.toBossTwoId in ('"+toBossTwoId+"')");
		}
		if(toBossOneId!=0){
			sBuffer.append(" and A.toBossOneId in ('"+toBossOneId+"')");
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		if(StrKit.notBlank(state)){
			sBuffer.append(" and TM.state in ('"+state+"')");
		}
		sBuffer.append(" ORDER BY TM.state asc,A.createDate DESC,A.id desc ");
		sBuffer.append(" ) a ");
		Record record =Db.findFirst(sBuffer.toString(),params.toArray());
		return record;	
	}
	/**
	 * 通过审核
	 * @param id
	 * @param checktime
	 * @return
	 */
	public int doPass(String id, String checktime,String userName,Double money) {
		return Db.update("update t_bus_user_changeToMoney A left join t_bus_user U on U.id=A.userId set A.state=3 ,A.updateTime=?,U.money=U.money + ?,A.changeName=? where A.id=?",checktime,money,userName,id);
	}
	/**
	 * 驳回审核
	 * @param id
	 * @param checktime
	 * @return
	 */
	/*public int doReturn(String id, String checktime,int tmJypoint) {
		return Db.update("update t_bus_user_changeToMoney set state=2 ,updateTime=? where id=?",checktime,id);
	}*/
	public int doReturn(String id, String checktime,Double absJypoint,String userName) {
		return Db.update("update t_bus_user_changeToMoney A left join t_bus_user U on U.id=A.userId set A.state=2 ,A.updateTime=?,U.jypoint=U.jypoint + ?,A.changeName=? where A.id=?",checktime,absJypoint,userName,id);
	}
	/**
	 * 查找当前用户
	 */

	public Record findById(int id){
		return Db.findFirst("SELECT A.*,TM.changeName,TM.orderNo AS tmOrderNo,TM.state AS tmState,TM.money AS tmMoney,TM.jypoint AS tmJypoint,TM.id AS tmId,S.trueName, SE.sellerName AS sellerName, ST.title AS storeName "
				+ "FROM t_bus_user_changeToMoney AS TM "
				+ "inner join t_bus_user A on TM.userId=A.id "
				+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
				+ "left join t_bus_seller SE on SE.id=A.toBossThreeId "
				+ "left join t_dict_store ST on ST.id=A.toBossTwoId "
				+ "where TM.id=?",id);
	}
	public int findUserId(String id) {
		// TODO Auto-generated method stub
		return Db.findById("t_bus_user_changeToMoney", id).get("userId");
	}
	public User findUser(int userId) {
		// TODO Auto-generated method stub
		return User.me().findById(userId);
	}
	/**
	 * 查找转换单据
	 */
	public Record findCtoM(int id){
		return Db.findById("t_bus_user_changeToMoney", id);
	}
}
